/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.wordlist;

import java.net.URL;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Revivius
 */
public class DBci {

    public static void main(String[] args) throws Exception {

        // register the driver 
        String sDriverName = "org.sqlite.JDBC";
        Class.forName(sDriverName);

        // now we set up a set of fairly basic string variables to use in the body of the code proper
        String sTempDb = "words.db";
        String sJdbc = "jdbc:sqlite";
        String sDbUrl = sJdbc + ":" + sTempDb;
        // which will produce a legitimate Url for SqlLite JDBC :
        // jdbc:sqlite:hello.db
        int iTimeout = 30;
        String sMakeTable = "CREATE TABLE IF NOT EXISTS words (word text)";
        String sMakeSelect = "SELECT response from dummy";

        // create a database connection
        Connection conn = DriverManager.getConnection(sDbUrl);
        try {
            Statement createStmt = conn.createStatement();
            
            try {
                createStmt.setQueryTimeout(iTimeout);
                createStmt.executeUpdate(sMakeTable);
                
                PreparedStatement stmt = conn.prepareStatement(sMakeInsert);
                writeWords(stmt);
                /*
                stmt.executeUpdate(sMakeInsert);
                 ResultSet rs = stmt.executeQuery(sMakeSelect);
                 try {
                 while (rs.next()) {
                 String sResult = rs.getString("response");
                 System.out.println(sResult);
                 }
                 } finally {
                 try {
                 rs.close();
                 } catch (Exception ignore) {
                 }
                 }*/
            } finally {
                try {
                    createStmt.close();
                } catch (Exception ignore) {
                }
            }
        } finally {
            try {
                conn.close();
            } catch (Exception ignore) {
            }
        }
    }
    private static final String sMakeInsert = "INSERT INTO words VALUES(?)";

    private static void writeWords(PreparedStatement stmt) throws Exception {
        URL url = MainClass.class.getResource("out");
        Path path = Paths.get(url.toURI());

        Files.lines(path).forEach(s -> {
            try {
                stmt.setString(1, s);
                stmt.addBatch();
            } catch (SQLException ex) {
                Logger.getLogger(DBci.class.getName()).log(Level.SEVERE, null, ex);
            }
        });
        
        stmt.executeBatch();
    }

}
